MySQLTuner

MySQLTuner is a read-only diagnostic tool that analyzes a running MySQL or MariaDB server and suggests configuration and schema-related improvements based on observed workload statistics. It inspects server variables, status counters, and InnoDB metrics to highlight common bottlenecks such as insufficient buffer sizing, poor query cache assumptions, excessive connection usage, missing indexes, and slow query patterns. On WordPress VPS servers, it is commonly used after the site has real traffic to validate database sizing and stability.

Background and history

As MySQL became a default database for web applications, administrators needed a quick way to translate server counters into actionable tuning guidance without manually interpreting dozens of metrics. MySQLTuner emerged as a lightweight reporting script that summarizes important indicators and flags common misconfigurations. It remains popular because it is easy to run, requires no agent installation, and provides a structured output that can guide further investigation.

Adoption and where it’s commonly used

MySQLTuner is commonly used in:

• VPS and cloud servers running MySQL or MariaDB
• WordPress stacks (high read/write bursts, many short queries)
• Shared hosting environments for quick health checks
• Post-migration and post-upgrade validation

Maintained by

• Maintained by the MySQLTuner project community.

Best when to use

• The database has been running long enough to gather meaningful statistics (hours to days).
• You want a quick health snapshot before deeper tuning.
• You need to validate memory sizing for InnoDB on a specific VPS size.
• You suspect bottlenecks in connections, buffers, or slow queries.

Not suitable when

• You expect the tool to automatically apply safe changes (it only suggests).
• You have no representative workload yet (fresh server, no traffic, just restarted).
• You need query-level tracing or profiling (use the slow query log and proper analysis tools).
• You are running a heavily customized or managed database where changes must follow provider policy.

Compatibility notes

• Works with MySQL and MariaDB, but some recommendations differ by version.

• Output and thresholds can be misleading on:

  • freshly restarted servers
  • servers with bursty cron/backup jobs
  • hosts with aggressive caching layers (Redis, page cache)

• Requires credentials to read server variables and status counters.

• Some environments package MySQLTuner as mysqltuner, others as a separate script.

Safety and expectations

MySQLTuner recommendations are not guaranteed to be correct for every workload. Treat them as starting points and validate changes with measured performance and stability testing.

How it works

MySQLTuner connects to the database and reads:

• server variables (SHOW VARIABLES)
• server status counters (SHOW GLOBAL STATUS)
• InnoDB metrics (depending on version)
• sometimes schema information (tables, engines) if privileges allow

It then computes ratios and heuristics (hit rates, memory usage estimates, temp table usage, thread/connection behavior) and prints recommendations.

Installation

Debian/Ubuntu

Try distro packages first:

sudo apt update
sudo apt install -y mysqltuner

If not available in your repo, you can run it as a standalone script from the project source, but keep it under version control and document how you update it.

RHEL/Fedora/Rocky/AlmaLinux

sudo dnf install -y mysqltuner

Verify:

mysqltuner --version 2>/dev/null || true
command -v mysqltuner

Prerequisites

• MySQL or MariaDB running

• A database user with privileges to read variables and status:

  • Common minimum: PROCESS, REPLICATION CLIENT, SELECT on performance schema (varies by version)

• A representative runtime window:

  • Prefer at least several hours of normal traffic
  • Avoid running immediately after a restart if you want accurate cache hit rates

Create a minimally privileged MySQLTuner user

Use a dedicated user rather than root.

Connect as an admin user:

sudo mysql

Create a user (adjust host restrictions to your needs):

CREATE USER 'mysqltuner'@'localhost' IDENTIFIED BY 'REPLACE_WITH_STRONG_PASSWORD';

Grant common read-only privileges (works for many MariaDB/MySQL versions; adjust if your version differs):

GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'mysqltuner'@'localhost';
GRANT SELECT ON performance_schema.* TO 'mysqltuner'@'localhost';
FLUSH PRIVILEGES;

Exit:

EXIT;

Version differences

Privileges required can vary by MySQL/MariaDB version and configuration. If MySQLTuner reports missing permissions, grant only the specific additional privileges it requests, then re-run.

Run MySQLTuner safely (read-only)

Basic run (prompt for credentials):

mysqltuner

Run with explicit user:

mysqltuner --user mysqltuner --pass

Run targeting a socket (common on local servers):

mysqltuner --socket /var/run/mysqld/mysqld.sock

Run and save output for review:

mysqltuner --user mysqltuner --pass 2>&1 | tee /root/mysqltuner.$(date +%F_%H%M%S).log

Interpreting key sections (WordPress-focused)

MySQLTuner outputs many checks. These are the areas that most often matter for WordPress VPS operations.

InnoDB buffer pool sizing

What it indicates:

• Whether memory allocated to InnoDB caching is appropriate.

Operational guidance:

• For dedicated database servers, InnoDB buffer pool often becomes the largest consumer of RAM.
• For “all-in-one” WordPress VPS (web + DB), leave room for PHP workers, OS cache, and Redis.

Validate current settings:

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages%';"

Connections and threads

Symptoms:

• Too many max connections wastes memory.
• Thread cache too small increases thread creation overhead.

Validate:

mysql -e "SHOW VARIABLES LIKE 'max_connections';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Threads%';"

Temporary tables on disk

Symptoms:

• Too many temp tables created on disk can signal memory limits or query patterns.

Validate:

mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%';"
mysql -e "SHOW VARIABLES LIKE 'tmp_table_size';"
mysql -e "SHOW VARIABLES LIKE 'max_heap_table_size';"

Slow queries

MySQLTuner may flag slow query usage or recommend enabling slow query logs. For WordPress, slow queries often come from:

• heavy plugins
• missing indexes
• large wp_options autoload
• unbounded admin reports

Enable slow query logging carefully (see below) and analyze before tuning blindly.

Safe tuning workflow (recommended)

1. Ensure the database has representative uptime (hours/days) and load.
2. Run MySQLTuner and save output.
3. Identify the top 1–2 constraints (memory pressure, temp tables, connections, slow queries).
4. Validate with direct MySQL counters and OS-level monitoring.
5. Make one change at a time, document it, and measure impact.
6. Restart MySQL only when required and during a maintenance window.

Avoid “apply everything”

Applying multiple recommendations at once makes it hard to identify what helped or harmed. Some suggestions trade memory for performance; on small VPS instances this can cause swap growth or OOM kills.

Enable slow query log (practical and controlled)

This is often the highest-value next step after MySQLTuner flags query inefficiency.

Check current slow log settings

mysql -e "SHOW VARIABLES LIKE 'slow_query_log';"
mysql -e "SHOW VARIABLES LIKE 'slow_query_log_file';"
mysql -e "SHOW VARIABLES LIKE 'long_query_time';"

Enable slow log (temporary runtime change)

This takes effect immediately but may reset on restart unless persisted in config:

mysql -e "SET GLOBAL slow_query_log = 'ON';"
mysql -e "SET GLOBAL long_query_time = 1;"

Slow log overhead

Slow query logs can grow quickly on busy sites. Ensure log rotation is configured and avoid enabling extremely low thresholds on high-traffic production sites without planning.

To persist settings, add them to your MySQL/MariaDB configuration and restart during a maintenance window.

Troubleshooting

MySQLTuner shows “unknown” or cannot connect

Checks:

• Confirm MySQL/MariaDB is running:

sudo systemctl status mysql --no-pager 2>/dev/null || sudo systemctl status mariadb --no-pager

• Confirm socket path:

mysqladmin variables | grep -i socket || true

• Try explicit socket:

mysqltuner --socket /var/run/mysqld/mysqld.sock

Permission errors

If MySQLTuner reports missing privileges:

• Read the exact privilege requirement from its output.
• Add only what is required.

Show grants:

mysql -e "SHOW GRANTS FOR 'mysqltuner'@'localhost';"

Recommendations conflict with available RAM

MySQLTuner sometimes recommends larger buffers than your VPS can safely support.

Cross-check memory pressure:

free -h
vmstat 1 5

If swap is growing or OOM events occur, reduce buffer sizes and prioritize stability.

Security notes

• Do not run MySQLTuner with root credentials unless necessary.
• Store exported logs securely; they can reveal schema names and operational details.
• Keep database admin access local (socket) when possible and restrict user host to localhost.

Quick reference

Common usage

Goal	Command
--	-
Run interactively	mysqltuner
Run with user prompt	mysqltuner --user mysqltuner --pass
Run with socket	mysqltuner --socket /var/run/mysqld/mysqld.sock
Save output	mysqltuner ... 2>&1 | tee /root/mysqltuner.YYYY-MM-DD_HHMMSS.log

Useful validation commands

Check	Command
--	--
InnoDB buffer pool size	mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
Max connections	mysql -e "SHOW VARIABLES LIKE 'max_connections';"
Temp tables	mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%';"
Slow log status	mysql -e "SHOW VARIABLES LIKE 'slow_query_log';"